package org.tool
{
	import flash.data.SQLConnection;
	import flash.data.SQLStatement;
	import flash.events.SQLErrorEvent;
	import flash.events.SQLEvent;
	import flash.filesystem.File;
	import flash.utils.Dictionary;
	
	import mx.controls.Alert;

	public class DBUtility
	{		
       private var conn:SQLConnection;
       private var createTableStatement:SQLStatement;
       private var insertStatement:SQLStatement;
       private var queryStatement:SQLStatement;       
       private var updateUserSQL:SQLStatement;
       private var deleteUserSQL:SQLStatement;

		public function DBUtility()
		{
			
		}
		
		public function openDataBase(base:String):void {
			conn = new SQLConnection();
			conn.addEventListener(SQLErrorEvent.ERROR, errorHandler);
		
			var path:String = base + File.separator + "booklist.db";
			var dbFile:File = File.desktopDirectory.resolvePath(path);
			
			if (dbFile.exists){
				conn.addEventListener(SQLEvent.OPEN, openHandler);
			}	
			else{
				conn.addEventListener(SQLEvent.OPEN, createHandler);
			}		
		}
		
		public function createTable():void {
			var commentText:String = "Create Table Comments(CID INTEGER PRIMARY KEY," +
			       "Desc var(500), LastDate Date)";
			var categoryText:String = "Create Table CATEGORY(CATEGORY INTEGER PRIMARY," +
			     "Des var(40)";
			var tagText = "Create Table Tags(TID INTEGER PRIMARY KEY, KEYWORD VAR(40))";
			var sqlText:string = "Create Table Books(ID INTEGER PRIMARY KEY," + 
					"NAME VARCHAR(40), CATEGORY INTEGER FORIGN KEY, PUBLISHDATE DATE, STAR INTEGER," + 
					+ "CID INTEGER FOREIGN KEY";
					
		    createTableStatement = new SQLStatement();
		    createTableStatement.sqlConnection = conn;
		    createTableStatement.addEventListener(SQLEvent.RESULT,createTableHandler);
		    createTableStatement.addEventListener(SQLErrorEvent.ERROR,errorHandler);
		    
		    createTableStatement.text = commentText;
		    createTableStatement.executing();
		    
		    createTableStatement.text = categoryText;
		    createTableStatement.executing();
		    
		    createTableStatement.text = tagText;
		    createTableStatement.executing();
		    
		    createTableStatement.text = sqlText;
		    createTableStatement.execute();
		 
		}
		
		public function insertRecord(tableName:String,columns:Dictionary):void {
			var insertText:String = "INSERT INTO " + tableName + "(";
			var value:String;
			
		    for (var key:Object in columns){
		    	insertText += key + ",";
		    	value += columns[key] + ",";
		    }
		    
		    
		    insertText = insertText.substr(0, insertText.length-1);
		    value = value.substr(0, value.length -1);
		    insertText += insertText + ") " + "values(" + value + ")";
		    
		    insertStatement = new SQLStatement();
		    insertStatement.sqlConnection = conn;
		    insertStatement.addEventListener(SQLEvent.RESULT, insertHandler);
		    insertStatement.addEventListener(SQLErrorEvent.ERROR,errorHandler);
		    insertStatement.text = insertText; 	
		    insertStatement.execute();	 
		    
		    Alert.show(insertText, "SQL Text");
		}
		
		public function updateTable(table:String, data:Dictionary, conditions:Dictionary):void {
			var updateText:String = "update " + table + " set ";
			
			for (var key:Object in data){
				updateText += key + "=" + data[key] + ",";
			}			
			
			updateText = updateText.substr(0, updateText.length-1);
			updateText += " where ";
			
			for (var key:Object in conditions){
				updateText += key + "=" + conditions[key] + ",";
			}
			updateText = updateText.substr(0, updateText.length-1);
			
			updateStatement = new SQLStatement();
		    updateStatement.sqlConnection = conn;
		    updateStatement.addEventListener(SQLEvent.RESULT, updateHandler);
		    updateStatement.addEventListener(SQLErrorEvent.ERROR,errorHandler);
		    updateStatement.text = updateText; 	
		    updateStatement.execute();	 
		    
		    Alert.show(updateText, "SQL Text");
		}
		
		public function deleteRecord(table:String, conditions:Dictionary):void {
			var deleteText:String = "delete From " + table + " where ";
			
			for (var key:Object in conditions){
				deleteText += key + "=" + conditions[key] + ",";
			}
			deleteText = deleteText.substr(0, deleteText.length -1);
			
			deleteStatement = new SQLStatement();
		    deleteStatement.sqlConnection = conn;
		    deleteStatement.addEventListener(SQLEvent.RESULT, deleteHandler);
		    deleteStatement.addEventListener(SQLErrorEvent.ERROR,errorHandler);
		    deleteStatement.text = deleteText; 	
		    deleteStatement.execute();	 
		    
		    Alert.show(deleteText, "SQL Text");
		}
		
		public function query(table:String, columns:Array, conditions:Dictionary):void {
			var queryText:String = "select ";
			
			for (int i=0; i<columns.length; i++){
				queryText += columns[i]+",";
			}
			queryText.substr(0, queryText.length -1);
			queryText += " where ";
			
			for (var key:Object in columns){
				queryText += key + "=" + columns[key] + ",";
			}
			queryText.substr(0, queryText.length -1);
			
		    queryStatement = new SQLStatement();
		    queryStatement.sqlConnection = conn;
		    queryStatement.addEventListener(SQLEvent.RESULT, queryHandler);
		    queryStatement.addEventListener(SQLErrorEvent.ERROR,errorHandler);
		    queryStatement.text = queryText; 	
		    queryStatement.execute();	
		    		    
		    return queryStatement.getResult().data;
		}
		private function errorHandler(evt:SQLErrorEvent):void {
			var err:String = "Error Id:" + evt.error.errorID + "\nDetails:"
			 +  event.error.message;
			 
		    Alert.show(err, "Error");
		}
		
		private function openHandler(event:SQLEvent):void {
			Alert.show("The database has been opened successfully", "openHandler");
		}
		
		private function createHandler(event:SQLEvent):void {
		    Alert.show("The database has been created successfully", "createHandler");
		}

        private function createTableHandler(event:SQLEvent):void{
        	Alert.show("The table " + event.currentTarget.toString() + " has been created successfully!",
        	    "createTableHandler");
        }
        
        private function insertHandler(event:SQLEvent):void {
        	Alert.show("The table " + event.currentTarget.toString() + " has been insert into records!");
        }
        
        private function updateHandler(event:SQLEvent):void {
        	Alert.show("The table " + event.currentTarget.toString() + " has been updated!");
        }
        
        private function deleteHandler(event:SQLEvent):void {
        	Alert.show("The table " + event.currentTarget.toString() + " has been deleted!");
        }
        
        private function queryHandler(event:SQLEvent):void {
        	Alert.show("The table " + event.currentTarget.toString() + " has been queryed!");
        }
	}
}